package org.lc.scoretj.repository;

import org.lc.scoretj.model.Score;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ScoreRepository extends JpaRepository<Score, Integer> {
    
    List<Score> findByXueQe(String xueQe);
    
    List<Score> findByXueQeAndPeiYangCengCi(String xueQe, String peiYangCengCi);
    
    List<Score> findByXueQeAndPeiYangCengCiAndZhuanYe(String xueQe, String peiYangCengCi, String zhuanYe);
    
    List<Score> findByXueQeAndPeiYangCengCiAndZhuanYeAndDuiBie(String xueQe, String peiYangCengCi, String zhuanYe, String duiBie);
    
    List<Score> findByXueQeAndCourseCode(String xueQe, String courseCode);
    
    @Query("SELECT DISTINCT s.xueQe FROM Score s")
    List<String> findDistinctXueQe();
    
    @Query("SELECT DISTINCT s.peiYangCengCi FROM Score s WHERE s.xueQe = :xueQe")
    List<String> findDistinctPeiYangCengCiByXueQe(@Param("xueQe") String xueQe);
    
    @Query("SELECT DISTINCT s.zhuanYe FROM Score s WHERE s.xueQe = :xueQe AND s.peiYangCengCi = :peiYangCengCi")
    List<String> findDistinctZhuanYeByXueQeAndPeiYangCengCi(@Param("xueQe") String xueQe, @Param("peiYangCengCi") String peiYangCengCi);
    
    @Query("SELECT DISTINCT s.duiBie FROM Score s WHERE s.xueQe = :xueQe AND s.peiYangCengCi = :peiYangCengCi AND s.zhuanYe = :zhuanYe")
    List<String> findDistinctDuiBieByXueQeAndPeiYangCengCiAndZhuanYe(@Param("xueQe") String xueQe, @Param("peiYangCengCi") String peiYangCengCi, @Param("zhuanYe") String zhuanYe);
    
    @Query("SELECT DISTINCT s.courseCode, s.courseName FROM Score s WHERE s.xueQe = :xueQe")
    List<Object[]> findDistinctCoursesByXueQe(@Param("xueQe") String xueQe);
}